Selecting Data From Multiple Tables: SQL JOINS

SQL Tutorial Home

This lesson covers perhaps the most important element of the SQL language: the SQL JOIN operation. As you know, a database stores data in several tables. When you need to combine data from multiple tables you do a JOIN between the tables where the data is stored.

This article presents examples using the SQL JOIN clause.

Sample Tables

There are two sample tables: tours and cities.

Table tours

Tour_Name Best_Season Price Duration (days)
United States and Canada March to Sep 3600 8
United States Big Cities All year 4300 12
Africa Tour All year 2100 7
Brazil Beaches Dec to March 1800 8

Table cities

City_Name Type_of_City Tour_Name Days Staging
Washington culture United States and Canada 5
Washington culture United States Big Cities 5
Cairo culture Africa Tour 4
Rio de Janeiro beach Brazil Beaches 4
Johannesburg safari Africa Tour 3
Florianópolis beach Brazil Beaches 4
Quebec culture United States and Canada 3
New York culture United States Big Cities 7

To use the JOIN clause to combine the two tables, there must be a shared column appearing in both tables. In this database, the column is tour_name. It is easy to identify the shared column because it has the same name on both tables. In other databases, you have to look at the values as the shared column can have different names. The important thing is the values of the columns, as the JOIN operator creates pairs of records for those records having the same value on the shared column.

Two types of JOIN operators are presented: INNER JOIN and LEFT OUTER JOIN.

SQL INNER JOIN Operator

Here is a simple SQL JOIN query to obtain the pairs:


SELECT
            tour_name,
            best_season,
            price,
            city_name,
            type_of_city,
            days_staging
FROM tours INNER JOIN cities ON tours.tour_name = cities.tour_name

How is a pair of records created by the SQL INNER JOIN operator?. For each record on the first table (tours), every record on the second table (cities) having the same value in the columns specified in the ON clause (tour_name) is paired with the record from the first table.

Here is the result:

tour_name best_season price city_name type_of city days_staging
United States Canada March to Sep 3600 Quebec culture 3
United States Canada March to Sep 3600 Washington culture 5
United States Big Cities All year 4300 New York culture 7
United States Big Cities All year 4300 Washington culture 5
Africa Tour All year 2100 Johannesburg safari 3
Africa Tour All year 2100 Cairo safari 4
Brazil Beaches Dec to March 1800 Florianópolis beach 4
Brazil Beaches Dec to March 1800 Rio de Janeiro beach 4

Suppose you want to know what tours are available for a culture city for less than $ 3000. Here is the query:


SELECT
            tour_name,
            price
FROM tours INNER JOIN cities ON tours.tour_name = cities.tour_name
WHERE price < 3000
  AND  type_of_city = ‘culture’

The result is:

tour_name price
Africa Tour 2100.00

SQL OUTER JOIN Operator

In some cases a record in the first table does not have a counterpart record in the second table in order to form a pair. For example, there could be a tour_name not related with any city, perhaps because it is a tour to the jungle or a one week sailing trip. Here is a new version of the table tours:

Tour_Name Best_Season Price Duration (days)
United States and Canada March to Sep 3600 8
United States Big Cities All year 4300 12
Africa Tour All year 2100 7
Brazil Beaches Dec to March 1800 8
Caribbean: a sailing week May to October 5300 7
5 days in the Amazonas Jungle All year 900 5

If you want to obtain a list of the available tours, you can execute the first INNER JOIN again:


SELECT
        tour_name,
        best_season,
        price,
        city_name,
        type_of_city,
        days_staging
FROM tours INNER JOIN cities ON tours.tour_name = cities.tour_name

The tours Caribbean: a sailing week and 5 days in the Amazonas Jungle are not returned in the results as there is no associated city.

tour_name best_season price city_name type_of_city days_staging
United States Canada March to Sep 3600 Quebec culture 3
United States Canada March to Sep 3600 Washington culture 5
United States Big Cities All year 4300 New York culture 7
United States Big Cities All year 4300 Washington culture 5
Africa Tour All year 2100 Johannesburg safari 3
Africa Tour All year 2100 Cairo safari 4
Brazil Beaches Dec to March 1800 Florianópolis beach 4
Brazil Beaches Dec to March 1800 Rio de Janeiro beach 4

To include the other tours, you use the SQL LEFT OUTER JOIN. The SQL LEFT OUTER JOIN is does not discard a row with no counterpart on the second table.

Here is the query:


SELECT
      tour_name,
      best_season,
      price,
      city_name,
      type_of_city,
      days_staging
FROM tours LEFT OUTER JOIN cities ON tours.tour_name = cities.tour_name

In the results, you can see the tours without cites are included. The values of the columns from the second table are NULL, as this is the normal behavior of the SQL LEFT OUTER JOIN operator.

tour_name best_season price city_name type_of city days_staging
United States Canada March to Sep 3600 Quebec culture 3
United States Canada March to Sep 3600 Washington culture 5
United States Big Cities All year 4300 New York culture 7
United States Big Cities All year 4300 Washington culture 5
Africa Tour All year 2100 Johannesburg safari 3
Africa Tour All year 2100 Cairo safari 4
Brazil Beaches Dec to March 1800 Florianópolis beach 4
Brazil Beaches Dec to March 1800 Rio de Janeiro beach 4
Caribbean: a sailing week May to Oct 5300
5 days in the Amazonas Jungle All year 900

Closing Words

In this lesson you learned two of the SQL JOIN operators INNER JOIN and LEFT OUTER JOIN. There are other less frequently used JOIN operators you can explore now that you understand the basics. Keep going, learn SQL and increase your skills!

IN THIS PAGE